package com.F05.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import com.F05.actionform.F0507ActionForm;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class F0507DAO  extends DataOperations{
	Logger log = Logger.getLogger(F0507DAO.class);
	public F0507DAO(){
		
	}
    public F0507DAO(Connection connection){
        super(connection);
    }
    public List queryTotalList(F0507ActionForm actionForm) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select count(*) countNum,sum(total_amount) totalAmount");
        sql.append(" from f_collect_money ");
        sql.append(" where 1=1 ");
 
        if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and manufacturing_order >="+actionForm.getManufacturingOrder1());
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		sql.append(" and manufacturing_order <="+actionForm.getManufacturingOrder2());
    	}
    	if(!actionForm.getContractNum1().equals("")){
    		sql.append(" and contract_number >="+actionForm.getContractNum1());
    	}
    	if(!actionForm.getContractNum2().equals("")){
    		sql.append(" and contract_number <="+actionForm.getContractNum2());
    	}
    	if(!actionForm.getInvoiceDate1().equals("")){
    		sql.append(" and invoice_date >= convert(datetime,'"+actionForm.getInvoiceDate1()+"')");
    	}
    	if(!actionForm.getInvoiceDate2().equals("")){
    		sql.append(" and invoice_date <= convert(datetime,'"+actionForm.getInvoiceDate2()+"')");
    	}
    	if(!actionForm.getInvoiceNumber1().equals("")){
    		sql.append(" and invoice_number >='"+actionForm.getInvoiceNumber1()+"' ");
    	}
    	if(!actionForm.getInvoiceNumber2().equals("")){
    		sql.append(" and invoice_number <='"+actionForm.getInvoiceNumber2()+"' ");
    	}
    	
    	
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			map.put("countNum", CommonTools.null2String(rest.getString("countNum")));
    			map.put("totalAmount", CommonTools.convertCurrencyRMB(rest.getString("totalAmount")));
	
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
	public List queryList(F0507ActionForm actionForm) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select a.id,contract_number,invoice_number,manufacturing_order,invoice_date,a.customers_code, total_amount,");
    	sql.append(" esti_collect_date,real_collect_date ,b.customers_name ");
        sql.append(" from f_collect_money a,c_customers b ");
        sql.append(" where a.customers_code = b.customers_code ");
 
        if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >="+actionForm.getManufacturingOrder1());
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		sql.append(" and a.manufacturing_order <="+actionForm.getManufacturingOrder2());
    	}
    	if(!actionForm.getContractNum1().equals("")){
    		sql.append(" and a.contract_number >="+actionForm.getContractNum1());
    	}
    	if(!actionForm.getContractNum2().equals("")){
    		sql.append(" and a.contract_number <="+actionForm.getContractNum2());
    	}
    	if(!actionForm.getInvoiceDate1().equals("")){
    		sql.append(" and a.invoice_date >= convert(datetime,'"+actionForm.getInvoiceDate1()+"')");
    	}
    	if(!actionForm.getInvoiceDate2().equals("")){
    		sql.append(" and a.invoice_date <= convert(datetime,'"+actionForm.getInvoiceDate2()+"')");
    	}
    	if(!actionForm.getInvoiceNumber1().equals("")){
    		sql.append(" and a.invoice_number >='"+actionForm.getInvoiceNumber1()+"' ");
    	}
    	if(!actionForm.getInvoiceNumber2().equals("")){
    		sql.append(" and a.invoice_number <='"+actionForm.getInvoiceNumber2()+"' ");
    	}
    	
    	
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			map.put("contractNumber", CommonTools.null2String(rest.getString("contract_number")));
    			map.put("invoiceNumber", CommonTools.null2String(rest.getString("invoice_number")));
    			map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("invoiceDate", CommonTools.getDateTime(rest.getDate("invoice_date")));
				map.put("totalAmount", CommonTools.convertCurrencyRMB(rest.getString("total_amount")));
				map.put("estiCollectDate", CommonTools.getDateTime(rest.getDate("esti_collect_date")));
				map.put("realCollectDate", CommonTools.getDateTime(rest.getDate("real_collect_date")));
				map.put("customersName", CommonTools.null2String(rest.getString("customers_name")));
	
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
}
